package by.dezhits.liltweets.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import by.dezhits.liltweets.bo.Tweet;
import by.dezhits.liltweets.bo.User;
import by.dezhits.liltweets.content.contract.PostContract;
import by.dezhits.liltweets.content.contract.TweetContract;
import by.dezhits.liltweets.content.contract.UsersContract;

public class DBHelper extends SQLiteOpenHelper {

	public class PostFlags {
		public static final String NOT_SENT = "Not sent yet";

		public static final String SENT = "Sent";

		public static final String CANNOT_BE_SENT = "Cannot be sent";
	}

	/** The constant database version number. */
	private static final int DATABASE_VERSION = 1;

	/** The constant database file name. */
	private static final String DATABASE_NAME = "liltweets22.store.db";

	/** The Constant COOKIE_TABLE. */
	public static final String TWEETS_TABLE = "TWEETS22";

	public static final String SEARCH_RESULT_TWEETS_TABLE = "SEARCH_RESULT_TWEETS";

	public static final String POST_TABLE = "POST";

	public static final String USERS_TABLE = "USERS";

	/** The Constant COOKIE_COLUMNS. */
	public static final String[] TWEETS_COLUMNS = {
			TweetContract.TweetsColumns.ID, TweetContract.TweetsColumns.TEXT,
			TweetContract.TweetsColumns.USER_NAME,
			TweetContract.TweetsColumns.PROFILE_IMAGE_URL,
			TweetContract.TweetsColumns.SCREEN_NAME,
			TweetContract.TweetsColumns.CREATED_AT,
			TweetContract.TweetsColumns.RETWEETER,
			TweetContract.TweetsColumns.IMAGE_URL,
			TweetContract.TweetsColumns.USER_ID,
			TweetContract.TweetsColumns.LINK,
			TweetContract.TweetsColumns.HASHTAGS };

	public static final String[] POST_COLUMNS = { PostContract.PostColumns.ID,
			PostContract.PostColumns.STATUS, PostContract.PostColumns.TEXT };

	public static final String[] USERS_COLUMNS = {
			UsersContract.UsersColumns.ID, UsersContract.UsersColumns.NAME,
			UsersContract.UsersColumns.SCREEN_NAME,
			UsersContract.UsersColumns.PROFILE_IMAGE_URL,
			UsersContract.UsersColumns.FOLLOWERS_COUNT,
			UsersContract.UsersColumns.STATUSES_COUNT,
			UsersContract.UsersColumns.FRIENDS_COUNT };

	/** The Constant ID_COL_INDEX. */
	public static final int TWEETS_ID_COL_INDEX = 0;
	/** The Constant TEXT_COL_INDEX. */
	public static final int TWEETS_TEXT_COL_INDEX = 1;
	/** The Constant USER_NAME_COL_INDEX. */
	public static final int TWEETS_USER_NAME_COL_INDEX = 2;
	/** The Constant USER_PROFILE_IMAGE_URL_INDEX. */
	public static final int TWEETS_USER_PROFILE_IMAGE_URL_INDEX = 3;

	public static final int TWEETS_SCREEN_NAME_COL_INDEX = 4;

	public static final int TWEETS_CREATED_AT_COL_INDEX = 5;

	public static final int TWEETS_RETWEETER_COL_INDEX = 6;

	public static final int TWEETS_IMAGE_URL_COL_INDEX = 7;

	public static final int TWEETS_USER_ID_COL_INDEX = 8;

	public static final int TWEETS_LINK_COL_INDEX = 9;

	public static final int TWEETS_HASHTAGS_COL_INDEX = 10;

	public static final int POST_ID_COL_INDEX = 0;

	public static final int POST_STATUS_COL_INDEX = 1;

	public static final int POST_TEXT_COL_INDEX = 2;

	public static final int USERS_ID_COL_INDEX = 0;

	public static final int USERS_NAME_COL_INDEX = 1;

	public static final int USERS_SCREEN_NAME_COL_INDEX = 2;

	public static final int USERS_PROFILE_IMAGE_URL_COL_INDEX = 3;

	public static final int USERS_FOLLOWERS_COUNT_COL_INTEX = 4;

	public static final int USERS_STATUSES_COUNT_COL_INDEX = 5;

	public static final int USERS_FRIENDS_COUNT_COUNT_INDEX = 6;

	/** The Constant CREATE_COOKIE_TABLE_SQL. */
	public static final String CREATE_TWEETS_TABLE_SQL = "CREATE TABLE "
			+ TWEETS_TABLE + " (" + TWEETS_COLUMNS[TWEETS_ID_COL_INDEX]
			+ " INTEGER PRIMARY KEY, " + TWEETS_COLUMNS[TWEETS_TEXT_COL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ TWEETS_COLUMNS[TWEETS_USER_NAME_COL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ TWEETS_COLUMNS[TWEETS_USER_PROFILE_IMAGE_URL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ TWEETS_COLUMNS[TWEETS_SCREEN_NAME_COL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ TWEETS_COLUMNS[TWEETS_CREATED_AT_COL_INDEX]
			+ " VARCHAR NOT NULL," + TWEETS_COLUMNS[TWEETS_RETWEETER_COL_INDEX]
			+ " VARCHAR, " + TWEETS_COLUMNS[TWEETS_IMAGE_URL_COL_INDEX]
			+ " VARCHAR, " + TWEETS_COLUMNS[TWEETS_USER_ID_COL_INDEX]
			+ "  VARCHAR NOT NULL, " + TWEETS_COLUMNS[TWEETS_LINK_COL_INDEX]
			+ " VARCHAR, " + TWEETS_COLUMNS[TWEETS_HASHTAGS_COL_INDEX]
			+ " VARCHAR)";

	public static final String CREATE_SEARCH_RESULT_TWEETS_TABLE_SQL = "CREATE TABLE "
			+ SEARCH_RESULT_TWEETS_TABLE
			+ " ("
			+ TWEETS_COLUMNS[TWEETS_ID_COL_INDEX]
			+ " INTEGER PRIMARY KEY, "
			+ TWEETS_COLUMNS[TWEETS_TEXT_COL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ TWEETS_COLUMNS[TWEETS_USER_NAME_COL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ TWEETS_COLUMNS[TWEETS_USER_PROFILE_IMAGE_URL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ TWEETS_COLUMNS[TWEETS_SCREEN_NAME_COL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ TWEETS_COLUMNS[TWEETS_CREATED_AT_COL_INDEX]
			+ " VARCHAR NOT NULL,"
			+ TWEETS_COLUMNS[TWEETS_RETWEETER_COL_INDEX]
			+ " VARCHAR, "
			+ TWEETS_COLUMNS[TWEETS_IMAGE_URL_COL_INDEX]
			+ " VARCHAR, "
			+ TWEETS_COLUMNS[TWEETS_USER_ID_COL_INDEX]
			+ "  VARCHAR NOT NULL, "
			+ TWEETS_COLUMNS[TWEETS_LINK_COL_INDEX]
			+ " VARCHAR, "
			+ TWEETS_COLUMNS[TWEETS_HASHTAGS_COL_INDEX]
			+ " VARCHAR)";

	public static final String CREATE_USERS_TABLE_SQL = "CREATE TABLE "
			+ USERS_TABLE + " (" + USERS_COLUMNS[USERS_ID_COL_INDEX]
			+ " INTEGER PRIMARY KEY, " + USERS_COLUMNS[USERS_NAME_COL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ USERS_COLUMNS[USERS_SCREEN_NAME_COL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ USERS_COLUMNS[USERS_PROFILE_IMAGE_URL_COL_INDEX]
			+ " VARCHAR NOT NULL, "
			+ USERS_COLUMNS[USERS_FOLLOWERS_COUNT_COL_INTEX]
			+ " INTEGER NOT NULL, "
			+ USERS_COLUMNS[USERS_STATUSES_COUNT_COL_INDEX]
			+ " INTEGER NOT NULL, "
			+ USERS_COLUMNS[USERS_FRIENDS_COUNT_COUNT_INDEX]
			+ " INTEGER NOT NULL)";

	public static final String CREATE_POST_TABLE_SQL = "CREATE TABLE "
			+ POST_TABLE + " (" + POST_COLUMNS[POST_ID_COL_INDEX]
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ POST_COLUMNS[POST_STATUS_COL_INDEX] + " VARCHAR NOT NULL, "
			+ POST_COLUMNS[POST_TEXT_COL_INDEX] + " VARCHAR NOT NULL, "
			+ "UNIQUE (" + POST_COLUMNS[POST_TEXT_COL_INDEX] + "))";

	/** The Constant DROP_COOKIE_TABLE_SQL. */
	public static final String DROP_TWEETS_TABLE_SQL = "DROP TABLE IF EXISTS "
			+ TWEETS_TABLE;

	public static final String DELETE_TWEETS_TABLE_SQL = "DELETE FROM "
			+ TWEETS_TABLE;

	/**
	 * Instantiates a new dB helper.
	 * 
	 * @param context
	 *            the context
	 * @param name
	 *            the name
	 * @param factory
	 *            the factory`
	 * @param version
	 *            the version
	 */
	public DBHelper(final Context context, final CursorFactory factory) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
	 * .SQLiteDatabase)
	 */
	@Override
	public final void onCreate(final SQLiteDatabase db) {
		try {
			db.beginTransaction();
			db.execSQL(CREATE_TWEETS_TABLE_SQL);
			db.execSQL(CREATE_SEARCH_RESULT_TWEETS_TABLE_SQL);
			db.execSQL(CREATE_POST_TABLE_SQL);
			db.execSQL(CREATE_USERS_TABLE_SQL);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
	 * .SQLiteDatabase, int, int)
	 */
	@Override
	public final void onUpgrade(final SQLiteDatabase db, final int oldVersion,
			final int newVersion) {
		try {
			db.beginTransaction();
			db.execSQL(DROP_TWEETS_TABLE_SQL);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		onCreate(db);
	}

	public Cursor getTweets(String selection, String[] selectionArgs,
			String sortOrder) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(DBHelper.TWEETS_TABLE, null, selection,
				selectionArgs, null, null, sortOrder);
		return cursor;
	}

	public void clearTweetsTable() {
		SQLiteDatabase db = getWritableDatabase();
		try {
			db.beginTransaction();
			db.execSQL(DELETE_TWEETS_TABLE_SQL);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	public void addTweet(Tweet tweet, String table) {
		SQLiteDatabase db = getWritableDatabase();
		db.insert(table, null, getContentValues(tweet));
	}

	public void addPost(String text, String status) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(DBHelper.POST_COLUMNS[DBHelper.POST_STATUS_COL_INDEX],
				status);
		values.put(DBHelper.POST_COLUMNS[DBHelper.POST_TEXT_COL_INDEX], text);
		db.insertWithOnConflict(DBHelper.POST_TABLE, null, values,
				SQLiteDatabase.CONFLICT_REPLACE);
	}

	/**
	 * Gets the content values for PersistedCookie POJO.
	 * 
	 * @param pTask
	 *            cookie POJO
	 * @return the content values
	 */
	private ContentValues getContentValues(Tweet t) {
		ContentValues values = new ContentValues();
		values.put(DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_ID_COL_INDEX],
				t.getId());
		values.put(DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_TEXT_COL_INDEX],
				t.getText());
		values.put(
				DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_USER_NAME_COL_INDEX],
				t.getUserName());
		values.put(DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_USER_ID_COL_INDEX],
				t.getUserId());
		values.put(
				DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_SCREEN_NAME_COL_INDEX],
				t.getScreenName());
		values.put(
				DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_USER_PROFILE_IMAGE_URL_INDEX],
				t.getProfileImageUrl());
		values.put(
				DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_CREATED_AT_COL_INDEX],
				t.getCreatedAt());
		values.put(
				DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_RETWEETER_COL_INDEX],
				t.getRetweeter());
		values.put(
				DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_IMAGE_URL_COL_INDEX],
				t.getImageUrl());
		values.put(DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_LINK_COL_INDEX],
				t.getLink());
		values.put(DBHelper.TWEETS_COLUMNS[DBHelper.TWEETS_HASHTAGS_COL_INDEX],
				t.getHashtags());
		return values;
	}

	public Cursor getPosts(String selection, String[] selectionArgs,
			String sortOrder) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(DBHelper.POST_TABLE, null, selection,
				selectionArgs, null, null, sortOrder);
		return cursor;
	}

	public void deleteTweets(String selection) {
		SQLiteDatabase db = getReadableDatabase();
		db.delete(TWEETS_TABLE, selection, null);
	}

	public Cursor getSearchResultTweets(String selection,
			String[] selectionArgs, String sortOrder) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(DBHelper.SEARCH_RESULT_TWEETS_TABLE, null,
				selection, selectionArgs, null, null, sortOrder);
		return cursor;
	}

	public void clearSearchResultTweetsTable() {
		SQLiteDatabase db = getReadableDatabase();
		db.delete(SEARCH_RESULT_TWEETS_TABLE, null, null);

	}

	public void deletePosts(String selection) {
		SQLiteDatabase db = getReadableDatabase();
		db.delete(POST_TABLE, selection, null);
	}

	public void deleteUser(String selection) {
		SQLiteDatabase db = getReadableDatabase();
		db.delete(USERS_TABLE, selection, null);		
	}

	public long addUser(User user) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(USERS_COLUMNS[DBHelper.USERS_ID_COL_INDEX], user.getId());
		values.put(USERS_COLUMNS[DBHelper.USERS_NAME_COL_INDEX], user.getName());
		values.put(USERS_COLUMNS[DBHelper.USERS_SCREEN_NAME_COL_INDEX], user.getScreenName());
		values.put(USERS_COLUMNS[DBHelper.USERS_ID_COL_INDEX], user.getId());
		values.put(USERS_COLUMNS[DBHelper.USERS_PROFILE_IMAGE_URL_COL_INDEX], user.getProfileImageUrl());
		values.put(USERS_COLUMNS[DBHelper.USERS_FOLLOWERS_COUNT_COL_INTEX], user.getFollowersCount());
		values.put(USERS_COLUMNS[DBHelper.USERS_STATUSES_COUNT_COL_INDEX], user.getStatusesCount());
		values.put(USERS_COLUMNS[DBHelper.USERS_FRIENDS_COUNT_COUNT_INDEX], user.getFriendsCount());
		return db.insertWithOnConflict(DBHelper.USERS_TABLE, null, values,
				SQLiteDatabase.CONFLICT_REPLACE);
	}

	public Cursor getUser(String selection, String[] selectionArgs,
			String sortOrder) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(DBHelper.USERS_TABLE, null, selection,
				selectionArgs, null, null, sortOrder);
		return cursor;
	}

}
